Energy-Water Flows from the IM3 GCAM-USA Scenarios

For the EPRI’s WET Forum

• Goal: plot a water sankey, energy sankey, energy-water sankey (even if a partial one), show evolution over time
• Let’s hand-pick the relevant queries. Once finalized, we can use rgcam to query the data.

Query and load IM3 data

Run this query_im3_scen("epri") only once to query from remote IM3 databases. Once a .dat file is created, we can load the existing project data by loadProject(proj = "im3scen_epri.dat").

# query the data
# im3_epri <- query_im3_scen("epri")

# load the data
im3_epri <- loadProject(proj = paste0("../", data_dir, "im3scen_epri.dat"))

# scenarios and queries 
listScenarios(im3_epri)

[1] "rcp45cooler_ssp3" "rcp45cooler_ssp5" "rcp45hotter_ssp3" "rcp45hotter_ssp5" "rcp85cooler_ssp3"
[6] "rcp85cooler_ssp5" "rcp85hotter_ssp3" "rcp85hotter_ssp5"

listQueries(im3_epri)

 [1] "resource supply curves"                                    
 [2] "resource production"                                       
 [3] "resource production by tech and vintage"                   
 [4] "basin level available runoff"                              
 [5] "total groundwater available"                               
 [6] "water withdrawals by water mapping source"                 
 [7] "water withdrawals by tech"                                 
 [8] "water withdrawals by state, sector, basin (includes desal)"
 [9] "water withdrawals by water source (runoff vs. groundwater)"
[10] "elec energy input by elec gen tech and cooling tech"       
[11] "elec water withdrawals by gen tech and cooling tech"       
[12] "elec gen by gen tech and cooling tech (incl cogen)"        
[13] "elec energy input by elec gen tech"                        
[14] "elec gen by gen tech and cooling tech"                     
[15] "elec water withdrawals by gen tech"                        
[16] "elec td inputs and outputs"                                
[17] "elec consumption by demand sector"                         

Water Sankey

Overall required information

• water availability by source (e.g., groundwater, surface water, desalination)
• water use by categories (e.g., agriculture, industry, domestic, power etc)
• water use by end-use sectors (e.g., crops, industry, electricity etc)

GCAM queries

• availability
  • resource supply curves
  • basin level available runoff
  • total runoff available
  • total groundwater available
• use by source
  • resource production
  • resource production by tech and vintage
  • water withdrawals by water source (runoff vs. groundwater)
• use by region/basin
  • water withdrawals by region
• use by category
  • water withdrawals by water mapping source
  • water withdrawals by tech
  • water withdrawals by tech (all elec)
  • water withdrawals by state, sector, basin (includes desal)

# get queries 
# availability 
resourceSupplyCurves <- getQuery(im3_epri, "resource supply curves") %>% filter_CONUSregions()
basinLevelAvailableRunoff <- getQuery(im3_epri, "basin level available runoff") %>% filter_CONUSregions() 
# totalRunoffAvailable <- getQuery(im3_epri, "total runoff available") %>% filter_CONUSregions()
totalGroundwaterAvailable <- getQuery(im3_epri, "total groundwater available") %>% filter_CONUSregions()
# production 
resourceProduction <- getQuery(im3_epri, "resource production") %>% filter_CONUSregions()
resourceProductionByTechVintage <- getQuery(im3_epri, "resource production by tech and vintage") %>% filter_CONUSregions()
waterWithdrawalsByWaterSource <- getQuery(im3_epri, "water withdrawals by water source (runoff vs. groundwater)") %>% filter_CONUSregions()
# use by region/basin 
# waterWithdrawalsByRegion <- getQuery(im3_epri, "water withdrawals by region") %>% filter_CONUSregions()
waterWithdrawalsByStateSectorBasin <- getQuery(im3_epri, "water withdrawals by state, sector, basin (includes desal)") %>% filter_CONUSregions()
# use by category 
waterWithdrawalsByWaterMappingSource <- getQuery(im3_epri, "water withdrawals by water mapping source") %>% filter_CONUSregions()
waterWithdrawalsByTech <- getQuery(im3_epri, "water withdrawals by tech") %>% filter_CONUSregions()
# waterWithdrawalsByTechAllElec <- getQuery(im3_epri, "water withdrawals by tech (all elec)") %>% filter_CONUSregions()

data_tables_water <- list(
  "resourceSupplyCurves" = resourceSupplyCurves,
  "basinLevelAvailableRunoff" = basinLevelAvailableRunoff,
  # "totalRunoffAvailable" = totalRunoffAvailable,
  "totalGroundwaterAvailable" = totalGroundwaterAvailable,
  "resourceProduction" = resourceProduction,
  "resourceProductionByTechVintage" = resourceProductionByTechVintage,
  "waterWithdrawalsByWaterSource" = waterWithdrawalsByWaterSource,
  # "waterWithdrawalsByRegion" = waterWithdrawalsByRegion,
  "waterWithdrawalsByStateSectorBasin" = waterWithdrawalsByStateSectorBasin,
  "waterWithdrawalsByWaterMappingSource" = waterWithdrawalsByWaterMappingSource,
  "waterWithdrawalsByTech" = waterWithdrawalsByTech
  # "waterWithdrawalsByTechAllElec" = waterWithdrawalsByTechAllElec
)

# print column names of each datatable
lapply(data_tables_water, function(x) colnames(x))

$resourceSupplyCurves
[1] "Units"       "scenario"    "region"      "resource"    "subresource" "grade"       "year"       
[8] "value"      

$basinLevelAvailableRunoff
[1] "Units"       "scenario"    "region"      "basin"       "subresource" "year"        "value"      

$totalGroundwaterAvailable
[1] "Units"       "scenario"    "region"      "resource"    "subresource" "grade"       "year"       
[8] "value"      

$resourceProduction
[1] "Units"    "scenario" "region"   "resource" "year"     "value"   

$resourceProductionByTechVintage
[1] "Units"       "scenario"    "region"      "resource"    "subresource" "technology"  "year"       
[8] "value"      

$waterWithdrawalsByWaterSource
[1] "Units"       "scenario"    "region"      "resource"    "subresource" "year"        "value"      

$waterWithdrawalsByStateSectorBasin
[1] "Units"      "scenario"   "region"     "sector"     "subsector"  "technology" "year"       "value"     

$waterWithdrawalsByWaterMappingSource
[1] "Units"    "scenario" "region"   "input"    "year"     "value"   

$waterWithdrawalsByTech
[1] "Units"      "scenario"   "region"     "sector"     "subsector"  "technology" "year"       "value"     

# print the first few rows of each datatable
lapply(data_tables_water, function(x) (x))

$resourceSupplyCurves

$basinLevelAvailableRunoff

$totalGroundwaterAvailable

$resourceProduction

$resourceProductionByTechVintage

$waterWithdrawalsByWaterSource

$waterWithdrawalsByStateSectorBasin

$waterWithdrawalsByWaterMappingSource

$waterWithdrawalsByTech

NA

Let’s process each piece to prepare the format of: scenario, source, target, year, value. Scenario and year could be filtered for each Sankey.

Water Availability

unique(resourceSupplyCurves$resource)

 [1] "onshore wind resource"                      "geothermal"                                
 [3] "coal"                                       "crude oil"                                 
 [5] "natural gas"                                "unconventional oil"                        
 [7] "traditional biomass"                        "Residue"                                   
 [9] "Scavenging_Other_Rsrc"                      "uranium"                                   
[11] "onshore carbon-storage"                     "Atlantic Ocean Seaboard_water withdrawals" 
[13] "Churchill_water withdrawals"                "Fraser_water withdrawals"                  
[15] "Great Lakes_water withdrawals"              "Hudson Bay Coast_water withdrawals"        
[17] "Mackenzie_water withdrawals"                "Northwest Territories_water withdrawals"   
[19] "Saskatchewan-Nelson_water withdrawals"      "St Lawrence_water withdrawals"             
[21] "Baja California_water withdrawals"          "Grijalva-Usumacinta_water withdrawals"     
[23] "Isthmus of Tehuantepec_water withdrawals"   "Mexico-Interior_water withdrawals"         
[25] "Mexico-Northwest Coast_water withdrawals"   "North Gulf_water withdrawals"              
[27] "Pacific Central Coast_water withdrawals"    "Papaloapan_water withdrawals"              
[29] "Rio Balsas_water withdrawals"               "Rio Lerma_water withdrawals"               
[31] "Rio Verde_water withdrawals"                "Yucatan Peninsula_water withdrawals"       
[33] "Arkansas White Red_water withdrawals"       "California River_water withdrawals"        
[35] "Great_water withdrawals"                    "Hawaii_water withdrawals"                  
[37] "Lower Colorado River_water withdrawals"     "Lower Mississippi River_water withdrawals" 
[39] "Mid Atlantic_water withdrawals"             "Missouri River_water withdrawals"          
[41] "New England_water withdrawals"              "Ohio River_water withdrawals"              
[43] "Pacific Northwest_water withdrawals"        "Pacific and Arctic Coast_water withdrawals"
[45] "Rio Grande River_water withdrawals"         "South Atlantic Gulf_water withdrawals"     
[47] "Tennessee River_water withdrawals"          "Texas Gulf Coast_water withdrawals"        
[49] "Upper Colorado River_water withdrawals"     "Upper Mississippi_water withdrawals"       

unique(resourceSupplyCurves$subresource)

 [1] "onshore wind resource"  "hydrothermal"           "coal"                   "crude oil"             
 [5] "natural gas"            "unconventional oil"     "traditional biomass"    "Residue"               
 [9] "Scavenging_Other"       "uranium"                "onshore carbon-storage" "groundwater"           
[13] "runoff"                

resourceSupplyCurves_water <- resourceSupplyCurves %>% filter(subresource %in% c("groundwater", "runoff")) %>% 
  filter(grade != "grade hist") %>% #exclude historical usage
  filter_basin_resource() %>%
  group_by(scenario, resource, subresource, year) %>% 
  summarise(value = sum(value)) %>% ungroup() %>% 
  remove_water_withdrawals_string() %>% 
  select(scenario, source = resource, target = subresource, year, value)

`summarise()` has grouped output by 'scenario', 'resource', 'subresource'. You can override using the `.groups` argument.

datatable(resourceSupplyCurves_water, filter = "top") 

plot_sankey(resourceSupplyCurves_water, "Water Availability by Basin and Resource Type")

NA

Total resource availability doesn’t change over time. This doesn’t look right because basin level available runoff (which uses max-annual-subresource instead of summing all grades) is much more and changes over time.

<resourceQuery title="total groundwater available">
    <axis1 name="grade">grade</axis1>
    <axis2 name="Year">available</axis2>
    <xPath buildList="true" dataName="output" group="false" sumAll="true">*[@type='resource']/*[@type='subresource' and @name='groundwater']/grade/available/node()</xPath>
</resourceQuery>

basinLevelAvailableRunoff is the one that uses max-annual-subresource instead of summing all grades

<resourceQuery title="basin level available runoff">
    <axis1 name="Basin">resource[@name]</axis1>
    <axis2 name="Year">max-annual-subresource[@year]</axis2>
    <xPath buildList="true" dataName="input" group="false" sumAll="false">*[@type = 'resource' and contains(@name, 'water withdrawals')]/*[@type = 'subresource' and contains(@name, 'runoff')]/max-annual-subresource/node()</xPath>
</resourceQuery>

Let’s use a combination of basinLevelAvailableRunoff and totalGroundwaterAvailable to get the total water availability by basin and year.

This looks better! Let’s move to water withdrawals.

Water Withdrawals

Let’s start by quantifying water withdrawals by water source (runoff vs. groundwater) for each basin.

• resourceProduction gives ALL resources, would need to filter by _water withdrawals. BUT this will give TOTAL basin level production (as basins are resources) and not by water source (runoff vs. groundwater) which are subresources.
• resourceProductionByTechVintage is the most detailed resource query, with information for regions, resources (water withdrawals), subresources (runoff, groundwater), production by year.

But let’s use a ready-made query waterWithdrawalsByWaterSource which gives water withdrawals by water source (runoff vs. groundwater) for each basin (resource).

• However, this still doesn’t have desalination. resourceProduction has desalination, but on the USA level. We will need to get desalination from waterWithdrawalsByStateSectorBasin.

# prepare desalination tables 
desalByBasin <- waterWithdrawalsByStateSectorBasin %>% 
    filter(region != "USA") %>% 
    filter(technology == "desalination") %>%
    filter_CONUSregions(subsector, basins_conus_resource) %>% 
    group_by(scenario, subsector, technology, year) %>%
    summarise(value = sum(value)) %>% ungroup() %>%
    select(scenario, source = technology, target = subsector, year, value)

`summarise()` has grouped output by 'scenario', 'subsector', 'technology'. You can override using the `.groups` argument.

desalByUseCategory <- waterWithdrawalsByStateSectorBasin %>% 
    filter(region != "USA") %>% 
    filter(technology == "desalination") %>% 
    filter_CONUSregions(subsector, basins_conus_resource) %>% 
    group_by(scenario, sector, technology, year) %>%
    summarise(value = sum(value)) %>% ungroup() %>%
    select(scenario, source = technology, target = sector, year, value)

`summarise()` has grouped output by 'scenario', 'sector', 'technology'. You can override using the `.groups` argument.

desalByState <- waterWithdrawalsByStateSectorBasin %>%
    filter(region != "USA") %>% 
    filter(technology == "desalination") %>% 
    filter_CONUSregions(subsector, basins_conus_resource) %>%   
    group_by(scenario, region, technology, year) %>%
    summarise(value = sum(value)) %>% ungroup() %>%
    select(scenario, source = technology, target = region, year, value)

`summarise()` has grouped output by 'scenario', 'region', 'technology'. You can override using the `.groups` argument.

plot_sankey(rbind(totalWaterAvailability, desalByBasin), "Total Water Availability by Basin and Resource Type")

df_waterWithdrawalsByWaterSource <- rbind(
  waterWithdrawalsByWaterSource %>% 
    filter_basin_resource() %>% 
    remove_water_withdrawals_string() %>%
    select(scenario, source = subresource, target = resource, year, value)
  , 
  desalByBasin
  )

datatable(df_waterWithdrawalsByWaterSource, filter = "top")

plot_sankey(df_waterWithdrawalsByWaterSource, "Water Withdrawals by Basin and Water Source")

NA

• Only 7 basins use groundwater in 2050 example above.

Water Withdrawals by State, Sector, and Basin

df_waterWithdrawalsByStateSectorBasin <- waterWithdrawalsByStateSectorBasin %>% 
  filter(region != "USA") %>% # exclude USA level data because it aggregated up from basin level data, but lacks the technology detail (name of the basin here)
  filter_CONUSregions(technology, basins_conus_resource) %>% # not sure why nonCONUS basins have crept in here at the technology level, but remove them 
  filter(technology != "desalination") %>% # we have desalination at the basin level in the previous plot 
  replace_after_irr_string() %>% # if this is removed, each basin will supply to it's own irrigation demand
  group_by(scenario, sector, technology, year) %>%
  summarise(value = sum(value)) %>% ungroup() %>%
  select(scenario, source = technology, target = sector, year, value)

`summarise()` has grouped output by 'scenario', 'sector', 'technology'. You can override using the `.groups` argument.

datatable(df_waterWithdrawalsByStateSectorBasin, filter = "top")

plot_sankey(df_waterWithdrawalsByStateSectorBasin, "Water Withdrawals by Use Category and Basin")

# let's try to piece this with waterWithdrawalsByWaterSource to the the water source type, basin, water use type flow
plot_sankey(rbind(df_waterWithdrawalsByStateSectorBasin, df_waterWithdrawalsByWaterSource), "Water Withdrawals by Use Category and Basin")

NA

TODO: Q: What is going on with Great Lakes? Why is it showing up in the water withdrawals but not in the water availability?

# exclude great lakes for now 
# we brought it back by borrowing from Canada. 
# df_waterWithdrawalsByStateSectorBasin <- df_waterWithdrawalsByStateSectorBasin %>% filter(source != "Great Lakes")

# plot_sankey(rbind(df_waterWithdrawalsByStateSectorBasin, df_waterWithdrawalsByWaterSource), "Water Withdrawals by Use Category and Basin")

Detailed Water Use

Let’s develop the water use categories further.

• both waterWithdrawalsByTech and waterWithdrawalsByTechAllElec are the same queries.
• get short basin names unique((waterWithdrawalsByTech_watCat %>% filter(grepl("biomassTree", subsector)))$subsector)

waterWithdrawalsByTech_watCat <- waterWithdrawalsByTech %>% map_water_use_to_categories() %>% filter_CONUSregions(region, states_conus)

watcategory_sector_use <- waterWithdrawalsByTech_watCat %>% 
  group_by(scenario, sector, watcategory, year) %>%
  summarise(value = sum(value)) %>% ungroup() %>%
  select(scenario, source = watcategory, target = sector, year, value)

`summarise()` has grouped output by 'scenario', 'sector', 'watcategory'. You can override using the `.groups` argument.

plot_sankey(rbind(df_waterWithdrawalsByStateSectorBasin, df_waterWithdrawalsByWaterSource, watcategory_sector_use), "Water Withdrawals by Use Category and Basin")

# use by subsector: same plot as previous after aggregation 
# watcategory_subsector_use <- waterWithdrawalsByTech_watCat %>% 
#   remove_GLUnames(subsector) %>% 
#   group_by(scenario, subsector, watcategory, year) %>%
#   summarise(value = sum(value)) %>% ungroup() %>%
#   select(scenario, source = watcategory, target = subsector, year, value)

# this is essentially the same as the previous plot
# plot_sankey(rbind(df_waterWithdrawalsByStateSectorBasin, df_waterWithdrawalsByWaterSource, watcategory_subsector_use), "Water Withdrawals by Use Category and Basin")

# expand electricity water use 
watcategory_subsector_use <- waterWithdrawalsByTech_watCat %>% 
  filter(sector == "electricity") %>%
  # remove_GLUnames(subsector) %>%
  group_by(scenario, sector, subsector, year) %>%
  summarise(value = sum(value)) %>% ungroup() %>%
  select(scenario, source = sector, target = subsector, year, value)

`summarise()` has grouped output by 'scenario', 'sector', 'subsector'. You can override using the `.groups` argument.

plot_sankey(rbind(df_waterWithdrawalsByStateSectorBasin, df_waterWithdrawalsByWaterSource, watcategory_sector_use, watcategory_subsector_use), "Water Withdrawals by Use Category and Basin")

Further technology level disaggregation for electricity water use

waterEnduseByTech <- waterWithdrawalsByTech_watCat %>% 
  filter(sector == "electricity") %>%
  clean_cooling_tech("technology", cooling_techs, cooling_techs_years) %>% 
  group_by(scenario, subsector, technology, year) %>%
  summarise(value = sum(value)) %>% ungroup() %>%
  select(scenario, source = subsector, target = technology, year, value)

`summarise()` has grouped output by 'scenario', 'subsector', 'technology'. You can override using the `.groups` argument.

plot_sankey(rbind(df_waterWithdrawalsByStateSectorBasin, df_waterWithdrawalsByWaterSource, watcategory_sector_use, watcategory_subsector_use, waterEnduseByTech), "Water Use by Source, Basin, Category, Sector, and Technology, and Basin")

NA

Water Requirement for Hydro

# add water requirement for hydro in the water diagram 
# ideally use hydro generation and create a water flow dynamically for all scenarios all years using the 3060 km3/EJ (853 MG/BBTU) coefficient 
# but right now, I am just going to create a single value

# hydro water requirement 

plot_sankey(rbind(df_waterWithdrawalsByStateSectorBasin, df_waterWithdrawalsByWaterSource, watcategory_sector_use, 
                  watcategory_subsector_use %>% 
                    # add hydro for rcp45cooler_ssp3 scenario and year 2050
                    add_row(scenario = "rcp45cooler_ssp3", source = "electricity", target = "hydro", year = 2050, value = 0.995 * 3060),
                  waterEnduseByTech) %>% rename_water_use_categories() %>% 
              mutate(source = ifelse(source == "runoff", "surface water", source)) %>% 
              mutate(target = ifelse(target == "industry", "industry (elec excluded)", target)),
            "Water Use by Source, Basin, Category, Sector, and Technology, and Basin")

  
plot_sankey(rbind(df_waterWithdrawalsByStateSectorBasin, df_waterWithdrawalsByWaterSource, watcategory_sector_use, 
                  watcategory_subsector_use %>% 
                    # add hydro for rcp45cooler_ssp3 scenario and year 2050
                    add_row(scenario = "rcp45cooler_ssp3", source = "electricity", target = "hydro", year = 2050, value = 0.995 * 3060 * 1e-3),
                  waterEnduseByTech) %>% rename_water_use_categories() %>% 
              mutate(source = ifelse(source == "runoff", "surface water", source)) %>% 
              mutate(target = ifelse(target == "industry", "industry (elec excluded)", target)),
            "Water Use by Source, Basin, Category, Sector, and Technology, and Basin")

  

# TODO: 
# labels
# time
# scenarios
# industry (elec excluded)
# relabel td sectors 
# runoff = surface water 
# annotations for levels (basins: HUC2)
# can colors the map as the plotly basins 
# avoid repeating colors

elecGenByGenTechCoolingTech_gUSA <- getQuery(im3_epri, "elec gen by gen tech and cooling tech (incl cogen)") %>% filter_CONUSregions()
elecGenByGenTechCoolingTech <- getQuery(im3_epri, "elec gen by gen tech and cooling tech") %>% filter_CONUSregions()
hydro_water_req <- elecGenByGenTechCoolingTech %>%
    filter(subsector == "hydro") %>%
    group_by(scenario, subsector, output, year) %>%
    summarise(value = sum(value)) %>% ungroup() %>% 
  mutate(source = "water_td_elec_W",
         hydro_water_req = value * 3060 * 1e-3) %>%
  select(scenario, source, target = subsector, year, value = hydro_water_req)

`summarise()` has grouped output by 'scenario', 'subsector', 'output'. You can override using the `.groups` argument.

Water Diagram for USA

# remove basins and electricity aggregations (only keep technologies)

# 1. prepare resources 

desal_totalUSA <- waterWithdrawalsByStateSectorBasin %>% 
    filter(region != "USA") %>% 
    filter(technology == "desalination") %>% 
    group_by(scenario, sector, technology, year) %>%
    summarise(value = sum(value)) %>% ungroup() %>%
    select(scenario, source = technology, target = sector, year, value)

`summarise()` has grouped output by 'scenario', 'sector', 'technology'. You can override using the `.groups` argument.

# create surface to groundwater shares by basin and apply those to use categories to determine amount of surface and groundwater used for each demand 
# 
# this calculates availabilty shares (which are not really relevant becasue we need withdrawal shares)
# totalWaterAvailability_USA <- rbind(
#   basinLevelAvailableRunoff %>% filter_basin_resource(basin) %>% remove_water_withdrawals_string(basin) %>% 
#     # we may have to switch the source and target here to start from water sources and go to basins
#     select(scenario, target = basin, source = subresource, year, value),
#   totalGroundwaterAvailable %>% 
#     filter_basin_resource() %>% remove_water_withdrawals_string() %>% 
#     filter(grade != "grade hist") %>% 
#     group_by(scenario, resource, subresource, year) %>%
#     summarise(value = sum(value)) %>% ungroup() %>%
#     select(scenario, target = resource, source = subresource, year, value)
# ) %>% group_by(scenario, source, year) %>%
#     summarise(value = sum(value)) %>% ungroup() 


waterWithdrawalsByWaterSource_shares_bybasin <- waterWithdrawalsByWaterSource %>% 
  filter_basin_resource() %>%
  remove_water_withdrawals_string() %>%
  group_by(scenario, region, resource, year) %>% mutate(share = value / sum(value)) %>% ungroup()

datatable(waterWithdrawalsByWaterSource_shares_bybasin, filter = "top")

waterWithdrawalsByStateSectorBasin_source <- waterWithdrawalsByStateSectorBasin %>% 
  # filter(scenario == "rcp85cooler_ssp5", year == "2050", sector == "water_td_an_W") %>%
  filter(region != "USA") %>% # exclude USA level data because it aggregated up from basin level data, but lacks the technology detail (name of the basin here)
  filter_CONUSregions(technology, basins_conus_resource) %>% # not sure why nonCONUS basins have crept in here at the technology level, but remove them 
  filter(technology != "desalination") %>% # we have desalination at the basin level in the previous plot 
  replace_after_irr_string() %>% # if this is removed, each basin will supply to it's own irrigation demand
  group_by(scenario, sector, technology, year) %>%
  summarise(value = sum(value)) %>% ungroup() %>%
  select(scenario, source = technology, target = sector, year, demand_withdraw = value) %>% 
  left_join(waterWithdrawalsByWaterSource_shares_bybasin, by = c("scenario", "source" = "resource", "year")) %>% 
  mutate(source_disagg = demand_withdraw * share) 

`summarise()` has grouped output by 'scenario', 'sector', 'technology'. You can override using the `.groups` argument.

datatable(waterWithdrawalsByStateSectorBasin_source, filter = "top")

# create 3 way shares among runoff gw and desal by using  desal from waterWithdrawalsByStateSectorBasin and runoff and gw from waterWithdrawalsByWaterSource
gwRunoffDesalShares <- waterWithdrawalsByWaterSource %>% 
  filter_basin_resource() %>%
  remove_water_withdrawals_string() %>% select(!c("Units", "region")) %>% 
  rbind(waterWithdrawalsByStateSectorBasin %>% 
          filter(region != "USA") %>% 
          filter(technology == "desalination") %>% 
          group_by(scenario, subsector, technology, year) %>%
          summarise(value = sum(value)) %>% ungroup() %>%
          select(scenario, subresource = technology, resource = subsector, year, value)
  ) %>% group_by(scenario, resource, year) %>% mutate(share = value / sum(value)) %>% ungroup()

`summarise()` has grouped output by 'scenario', 'subsector', 'technology'. You can override using the `.groups` argument.

waterWithdrawalsByStateSectorBasin_source_3 <- waterWithdrawalsByStateSectorBasin %>% 
  # filter(scenario == "rcp85cooler_ssp5", year == "2050", sector == "water_td_an_W") %>%
  filter(region != "USA") %>% # exclude USA level data because it aggregated up from basin level data, but lacks the technology detail (name of the basin here)
  filter_CONUSregions(technology, basins_conus_resource) %>% # not sure why nonCONUS basins have crept in here at the technology level, but remove them 
  filter(technology != "desalination") %>% # we have desalination at the basin level in the previous plot 
  replace_after_irr_string() %>% # if this is removed, each basin will supply to it's own irrigation demand
  group_by(scenario, sector, technology, year) %>%
  summarise(value = sum(value)) %>% ungroup() %>%
  select(scenario, source = technology, target = sector, year, demand_withdraw = value) %>% 
  left_join(gwRunoffDesalShares, by = c("scenario", "source" = "resource", "year")) %>% 
  mutate(source_disagg = demand_withdraw * share) 

`summarise()` has grouped output by 'scenario', 'sector', 'technology'. You can override using the `.groups` argument.

waterSourceUseCategories3 <- waterWithdrawalsByStateSectorBasin_source_3 %>% 
  group_by(scenario, subresource, target, year) %>% 
  summarise(value = sum(source_disagg)) %>% ungroup() %>% 
  select(scenario, source = subresource, target, year, value) %>% 
  # note we are aggregating up basin-level irrigation from desalination here 
  rbind(desal_totalUSA %>% replace_after_irr_string(target) %>% 
          group_by(scenario, source, target, year) %>% summarise(value = sum(value)) %>% ungroup()
        ) 

`summarise()` has grouped output by 'scenario', 'subresource', 'target'. You can override using the `.groups` argument.`summarise()` has grouped output by 'scenario', 'source', 'target'. You can override using the `.groups` argument.

plot_sankey(waterSourceUseCategories3, "Water Source and Use Category")  

plot_sankey(rbind(waterSourceUseCategories3, waterCateogryTech), "Water Source, Use Category, and End-Use")

# CONLCUSION: THIS IS EXACTLY THE SAME AS THE ONE PREPARE WIHTOUT 3 WAY SHARES                              

# US level supply and demand
waterSourceUseCategories <- waterWithdrawalsByStateSectorBasin_source %>% 
  group_by(scenario, subresource, target, year) %>% 
  summarise(value = sum(source_disagg)) %>% ungroup() %>% 
  select(scenario, source = subresource, target, year, value) %>% 
  # note we are aggregating up basin-level irrigation from desalination here 
  rbind(desal_totalUSA %>% replace_after_irr_string(target) %>% 
          group_by(scenario, source, target, year) %>% summarise(value = sum(value)) %>% ungroup()
        ) 

`summarise()` has grouped output by 'scenario', 'subresource', 'target'. You can override using the `.groups` argument.`summarise()` has grouped output by 'scenario', 'source', 'target'. You can override using the `.groups` argument.

plot_sankey(waterSourceUseCategories, "Water Source and Use Category")   

NA

# water source, use category, and end-use 
waterCateogryTech <- 
  # we will use different details for each use category 
  rbind(
    # electricity on tech level
    waterWithdrawalsByTech_watCat %>% 
      filter(watcategory == "water_td_elec_W") %>% 
      clean_cooling_tech("technology", cooling_techs, cooling_techs_years) %>%
      group_by(scenario, watcategory, technology, year) %>%
      summarise(value = sum(value)) %>% ungroup() %>% 
      select(scenario, source = watcategory, target = technology, year, value)
    , 
    # everything else on sector level 
    waterWithdrawalsByTech_watCat %>% 
      filter(watcategory != "water_td_elec_W") %>% 
      group_by(scenario, watcategory, sector, year) %>%
      summarise(value = sum(value)) %>% ungroup() %>%
      select(scenario, source = watcategory, target = sector, year, value)
  )

`summarise()` has grouped output by 'scenario', 'watcategory', 'technology'. You can override using the `.groups` argument.`summarise()` has grouped output by 'scenario', 'watcategory', 'sector'. You can override using the `.groups` argument.

plot_sankey(rbind(waterSourceUseCategories, waterCateogryTech), "Water Source, Use Category, and End-Use")

# water source, basin, use category, and end-use
plot_sankey(rbind(df_waterWithdrawalsByWaterSource, df_waterWithdrawalsByStateSectorBasin, waterCateogryTech, hydro_water_req), "Water Source, Basin, Use Category, and End-Use")

# SWAPPED basins: water source, basin, use category, and end-use
water_source_target <- rbind(df_waterWithdrawalsByWaterSource %>% swap_source_target(), 
                  waterSourceUseCategories,
                  waterCateogryTech, hydro_water_req) %>% 
  complete(scenario, year, nesting(source, target), fill = list(value = 0)) %>% mutate(units = "km3") %>% 
  select(scenario, source, target, year, value, units)

plot_sankey(water_source_target, "Water Source, Basin, Use Category, and End-Use")

write_csv(water_source_target , paste0("../", data_dir, "water_source_target.csv"))

# water source, basin, use category, and end-use WITH BETTER COLORS 

waterSourceBasinsCategoriesUse_colors <- read_csv(paste0("../", data_dir, "waterSourceBasinsCategoriesUse.csv"))

Rows: 16861 Columns: 8-- Column specification ---------------------------------------------------------------------------------------------------------
Delimiter: ","
chr (6): scenario, target, source, target_color, source_color, flow_color
dbl (2): year, value
i Use `spec()` to retrieve the full column specification for this data.
i Specify the column types or set `show_col_types = FALSE` to quiet this message.

plot_sankey(waterSourceBasinsCategoriesUse_colors, "Water Source, Basin, Use Category, and End-Use")

# Combine source and target to get unique nodes and their colors
nodes <- waterSourceBasinsCategoriesUse_colors %>%
  select(source, source_color) %>%
  rename(node = source, node_color = source_color) %>%
  bind_rows(
    waterSourceBasinsCategoriesUse_colors %>%
      select(target, target_color) %>%
      rename(node = target, node_color = target_color)
  ) %>%
  distinct()

# Add color directly to node labels
node_labels <- unique(c(waterSourceBasinsCategoriesUse_colors$source, 
                        waterSourceBasinsCategoriesUse_colors$target))

# Map node colors to node labels
node_colors <- nodes$node_color[match(node_labels, nodes$node)]

unique(waterSourceBasinsCategoriesUse_colors[c("source", "source_color")])$source_color[match(node_labels, unique(waterSourceBasinsCategoriesUse_colors$source))]

 [1] "#005F73" "#00B1D1" "#005F73" "#00B1D1" "#005F73" "#00B1D1" "#005F73" "#00B1D1" "#005F73" "#00B1D1" "#005F73" "#00B1D1"
[13] "#005F73" "#00B1D1" "#005F73" "#00B1D1" "#005F73" "#00B1D1" "#005F73" "#00B1D1" "#005F73" "#00B1D1" "#005F73" "#00B1D1"
[25] "#005F73" "#00B1D1" "#005F73" NA        NA        NA        NA        NA        NA        NA        NA        NA       
[37] NA        NA        NA        NA        NA        NA        NA        NA        NA        NA        NA        NA       
[49] NA        NA        NA        NA        NA        NA        NA        NA        NA        NA        NA        NA       
[61] NA        NA        NA        NA        NA        NA        NA        NA        NA        NA        NA        NA       
[73] NA       

Energy Sankey

Overall information required:

• resource production (coal, gas, oil etc)
• energy inputs to electricity generation
  • input is resource
• electricity generation
• electricity use by categories
• electricity use by end uses

Queries:

• elec energy input by elec gen tech and cooling tech
• elec water withdrawals by gen tech and cooling tech
• elec gen by gen tech and cooling tech (incl cogen)
• elec energy input by elec gen tech
• elec gen by gen tech and cooling tech
• elec water withdrawals by gen tech
• elec td inputs and outputs
• elec consumption by demand sector

# read queries 
# GCAM-USA specific queries 
elecEnergyInputByElecGenTechCoolingTech_gUSA <- getQuery(im3_epri, "elec energy input by elec gen tech and cooling tech") %>% filter_CONUSregions()
elecWaterWithdrawByElecGenTechCoolingTech_gUSA <- getQuery(im3_epri, "elec water withdrawals by gen tech and cooling tech") %>% filter_CONUSregions()
elecGenByGenTechCoolingTech_gUSA <- getQuery(im3_epri, "elec gen by gen tech and cooling tech (incl cogen)") %>% filter_CONUSregions()
# generic GCAM queries
elecEnergyInputByElecGenTech <- getQuery(im3_epri, "elec energy input by elec gen tech") %>% filter_CONUSregions()
elecGenByGenTechCoolingTech <- getQuery(im3_epri, "elec gen by gen tech and cooling tech") %>% filter_CONUSregions()
elecWaterWithdrawByGenTech <- getQuery(im3_epri, "elec water withdrawals by gen tech") %>% filter_CONUSregions()
elecTDInputsOutputs <- getQuery(im3_epri, "elec td inputs and outputs") %>% filter_CONUSregions()
elecConsumptionByDemandSector <- getQuery(im3_epri, "elec consumption by demand sector") %>% filter_CONUSregions()

data_tables_ew <- list(
  "elecEnergyInputByElecGenTechCoolingTech_gUSA" = elecEnergyInputByElecGenTechCoolingTech_gUSA,
  "elecWaterWithdrawByElecGenTechCoolingTech_gUSA" = elecWaterWithdrawByElecGenTechCoolingTech_gUSA,
  "elecGenByGenTechCoolingTech_gUSA" = elecGenByGenTechCoolingTech_gUSA,
  "elecEnergyInputByElecGenTech" = elecEnergyInputByElecGenTech,
  "elecGenByGenTechCoolingTech" = elecGenByGenTechCoolingTech,
  "elecWaterWithdrawByGenTech" = elecWaterWithdrawByGenTech,
  "elecTDInputsOutputs" = elecTDInputsOutputs,
  "elecConsumptionByDemandSector" = elecConsumptionByDemandSector
)

# print column names of each datatable
lapply(data_tables_ew, function(x) colnames(x))

$elecEnergyInputByElecGenTechCoolingTech_gUSA
[1] "Units"      "scenario"   "region"     "sector"     "subsector"  "technology" "input"      "year"       "value"     

$elecWaterWithdrawByElecGenTechCoolingTech_gUSA
[1] "Units"      "scenario"   "region"     "sector"     "subsector"  "technology" "input"      "year"       "value"     

$elecGenByGenTechCoolingTech_gUSA
[1] "Units"      "scenario"   "region"     "sector"     "subsector"  "technology" "year"       "value"     

$elecEnergyInputByElecGenTech
[1] "Units"      "scenario"   "region"     "sector"     "subsector"  "technology" "input"      "year"       "value"     

$elecGenByGenTechCoolingTech
[1] "Units"      "scenario"   "region"     "sector"     "subsector"  "technology" "output"     "year"       "value"     

$elecWaterWithdrawByGenTech
[1] "Units"      "scenario"   "region"     "sector"     "subsector"  "technology" "input"      "year"       "value"     

$elecTDInputsOutputs
[1] "Units"    "scenario" "region"   "sector"   "year"     "value"   

$elecConsumptionByDemandSector
[1] "Units"    "scenario" "region"   "sector"   "input"    "year"     "value"   

# print the first few rows of each datatable
lapply(data_tables_ew, function(x) (x))

$elecEnergyInputByElecGenTechCoolingTech_gUSA

$elecWaterWithdrawByElecGenTechCoolingTech_gUSA

$elecGenByGenTechCoolingTech_gUSA

$elecEnergyInputByElecGenTech

$elecGenByGenTechCoolingTech

$elecWaterWithdrawByGenTech

$elecTDInputsOutputs

$elecConsumptionByDemandSector

NA

elec_source_target <- rbind(
  # water withdrawals by electricity generation technology
  elecWaterWithdrawByGenTech %>% 
    # mutate(value = value * 1e-1) %>% # change to x10 km3 for better visuals
    clean_cooling_tech("technology", cooling_techs, cooling_techs_years) %>%
    group_by(scenario, technology, input, year) %>%
    summarise(value = sum(value)) %>% ungroup() %>% 
    select(scenario, source = input , target = technology, year, value)
  ,
  elecEnergyInputByElecGenTech %>% 
    clean_cooling_tech("technology", cooling_techs, cooling_techs_years) %>%
    group_by(scenario, technology, input, year) %>%
    summarise(value = sum(value)) %>% ungroup() %>% 
    select(scenario, source = input , target = technology, year, value)
  ,
  # electricity generation by technology
  elecGenByGenTechCoolingTech %>% 
    clean_cooling_tech("technology", cooling_techs, cooling_techs_years) %>%
    group_by(scenario, subsector, technology, year) %>%
    summarise(value = sum(value)) %>% ungroup() %>% 
    select(scenario, source = technology, target = subsector, year, value)
  ,
  # bridge gen sector to electricity supply sector
  elecGenByGenTechCoolingTech %>%
    # sum by subsector
    group_by(scenario, subsector, output, year) %>%
    summarise(value = sum(value)) %>% ungroup() %>%
    rename(source = subsector, target = output) 
  ,
  # inputs to the end use sectors
  elecTDInputsOutputs %>%
    filter(grepl("elect_", sector)) %>%
    group_by(scenario, sector, year) %>%
    summarise(value = sum(value)) %>% ungroup() %>%
    # TODO: add electricity_net_ownuse, and create a losses "end-use"
    mutate(source = "electricity") %>%
    select(scenario, source, target = sector, year, value)
  ,
  # electricity consumption by demand sector
  elecConsumptionByDemandSector %>%
    group_by(scenario, sector, input, year) %>%
    summarise(value = sum(value)) %>% ungroup() %>%
    select(scenario, source = input, target = sector, year, value)
) %>% filter(source != target)

`summarise()` has grouped output by 'scenario', 'technology', 'input'. You can override using the `.groups` argument.`summarise()` has grouped output by 'scenario', 'technology', 'input'. You can override using the `.groups` argument.`summarise()` has grouped output by 'scenario', 'subsector', 'technology'. You can override using the `.groups` argument.`summarise()` has grouped output by 'scenario', 'subsector', 'output'. You can override using the `.groups` argument.`summarise()` has grouped output by 'scenario', 'sector'. You can override using the `.groups` argument.`summarise()` has grouped output by 'scenario', 'sector', 'input'. You can override using the `.groups` argument.

plot_sankey(elec_source_target, "Electricity Generation and Consumption")

# remove water from electricity diagram 
plot_sankey(elec_source_target %>% filter(source != "water_td_elec_W"), yr = 2050, "Electricity Generation and Consumption")